package com.vogue.supplymall.order.domain.provider;

import com.vogue.ssm.database.provider.BaseSqlProvider;
import com.vogue.ssm.database.util.DealStatus;

public class RefundOrderDetailSqlProvider extends BaseSqlProvider{
	  public String findRefundOrderDetail(Long refundOrderId) {
	        StringBuilder sql = new StringBuilder();
	        sql.append(" SELECT distinct")
	                .append(" ro.refundnumber ordernumber, ")
	                .append(" ro.finalprice totalprice, ")
	                .append(" 1 as totalquantity, ")
	                .append(" ro.freight, ")
	                .append(" ro.memo, ")
	                .append(" ro.createdate, ")
	                .append(" ro.finalprice, ")
	                .append(" ro.cause,")
	                .append(" oi.id oiid, ")
	                .append(" oi.itemprice, ")
	                .append(" rd.refundquantity itemquantity, ")
	                .append(" oi.storageid, ")
	                .append(" oi.`storage`, ")
	                .append(" oip.id oipid, ")
	                .append(" oip.pname, ")
	                .append(" oip.price, ")
	                .append(" oip.productid, ")
	                .append(" oip.attrplanid, ")
	                .append(" oip.punit, ")
	                .append(" oip.imgurl, ")
	                .append(" oip.weight, ")
	                .append(" os.shopid os_shopid, ")
	                .append(" os.entname os_entname, ")
	                .append(" os.shopname os_shopname, ")
	                .append(" ob.btel ob_btel, ")
	                .append(" ob.buyeraddress ob_address, ")
	                .append(" ob.buyername ob_name, ")
	                .append(" ob.buyertype ob_buyertype, ")
	                .append(" ob.entname ob_entname, ")
	                .append(" ob.storename ob_storename,")
	                .append(" rs.id statusid, ")
	                // 2018/2/26 add供应链侧是否拉取过订单
	                .append(" rs.sellerpullstate pullstate, ")
	                .append(" rs.refundstatus orderstatus, ")
	                .append(" rs.modifydate, ")
	                .append(" ffm.id ffm_id,")
	                .append(" ffm.`status` ffm_status,")
	                .append(" ffm.`type` ffm_type,")
	                .append(" ifnull(ffm.senddate,'') ffm_senddate,")
	                .append(" ifnull(ffm.arrivedate,'') ffm_arrivedate,")
	                .append(" ffm.number ffm_number,")
	                .append(" ffm.companycode ffm_companycode,")
	                .append(" CONVERT(rp.paymentmode, SIGNED) refundway")
	                ;
	        sql.append(" FROM ")
	                .append(" `refund_order` ro ")
	                .append(" join refund_detail rd on ro.id=rd.refundid")
	                .append(" INNER JOIN `order` o ON (o.id = ro.orderid) ")
	                .append(" INNER JOIN refund_payment rp ON (ro.id = rp.refundid)")
	                .append(" INNER JOIN refund_status rs ON (ro.id = rs.refundid) ")
	                .append(" INNER JOIN order_buyer ob ON (o.id = ob.orderid) ")
	                .append(" INNER JOIN order_seller os ON (o.id = os.orderid) ")
	                .append(" INNER JOIN order_item oi ON (o.id = oi.orderid) ")
	                .append(" INNER JOIN order_item_product oip ON ( ")
	                .append(" o.id = oip.orderid ")
	                .append(" AND oi.id = oip.orderitemid ")
	                .append("  and rd.oipid=oip.id")
	                .append(" ) ")
	                .append(" left join fulfillment ffm on ffm.orderid=ro.id")
	                ;
	        sql.append(" WHERE ")
	                .append(" ro.id = #{refundOrderId} ")
	                .append(" and o.disabledflag = 0 ")
	                .append(" order BY oi.createdate ");
	        return DealStatus.formatShadeSql(sql.toString());
	    }
	  
	  public  String findRefundOrderRefundUpload(Long refundOrderId) {
		  StringBuilder sql = new StringBuilder();
	        sql.append(" SELECT ")
	                .append(" ifnull(ru.`type`,0) ru_type,")
	                .append(" ifnull(ru.url,'') ru_url,")
	                .append(" ifnull(ru.size,0) ru_size")
	                ;
	        sql.append(" FROM ")
	                .append(" refund_upload ru ")
	                ;
	        sql.append(" WHERE ")
	                .append(" ru.refundid = #{refundOrderId} ");
	        return DealStatus.formatShadeSql(sql.toString());
	  }
	  
}
